﻿/*
 * Copyright (c) 2009, Thiago M. Sayao <thiago.sayao@gmail.com>
 * All rights reserved.
 * 
 * Redistribution and use in source and binary forms, with or without 
 * modification, are permitted provided that the following conditions are met:
 *  * Redistributions of source code must retain the above copyright notice, 
 *    this list of conditions and the following disclaimer.
 *  * Redistributions in binary form must reproduce the above copyright notice,
 *    this list of conditions and the following disclaimer in the documentation
 *    and/or other materials provided with the distribution.
 *  * Neither the name of the author nor the names of its contributors may be 
 *    used to endorse or promote products derived from this software without 
 *    specific prior written permission.
 * 
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE 
 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE 
 * ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE 
 * LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR 
 * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF 
 * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS 
 * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN 
 * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) 
 * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE 
 * POSSIBILITY OF SUCH DAMAGE.
 */

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
using DotNetBurner.Core;

namespace DotNetBurner.Model
{
    public enum TagListOrderBy : short
    {
        Name = 1,
        StoryCount = 2
    }

    public class Repository
    {
        string _connStr;

        public Repository(string connStr)
        {
            _connStr = connStr;
        }

        public string ConnectionString
        {
            get {
                return _connStr;
            }
            set {
                _connStr = value;
            }
        }

        public IEnumerable<SiteLink> GetSiteMap()
        {
            List<SiteLink> links = new List<SiteLink>();

            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                using (SqlCommand cmd = new SqlCommand("sp_sitemap_get", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    conn.Open();

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            DateTime lastModified = reader.GetDateTime("LastModified");
                        
                            SiteLink link = new SiteLink { 
                                                             StoryId = reader.GetInt32("StoryId"), 
                                                             Title = reader.GetString("Title"),
                                                             LastModified = lastModified
                                                         };

                            links.Add(link);
                        }
                    }
                }
            }

            return links;
        }

        IEnumerable<Category> GetAllCategories()
        {
            List<Category> categories = new List<Category>();

            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                using (SqlCommand cmd = new SqlCommand("sp_category_get_all", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    conn.Open();

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            short? parentId = null;

                            if (reader["ParentId"].GetType() != typeof(DBNull))
                                parentId = reader.GetInt16("ParentId");

                            Category category = new Category(reader.GetInt16("CategoryId"), parentId, reader.GetString("Name"));

                            categories.Add(category);
                        }
                    }
                }
            }

            return categories;
        }

        public virtual IEnumerable<Category> GetCategories()
        {
            var categs = GetAllCategories();

            //build the hierarchy -- Linq magic
            var firstLevels = (from r in categs
                                where r.ParentId == null
                                orderby r.Name
                                select new Category(
                                        r.CategoryId,
                                        r.ParentId,
                                        r.Name,
                                        r.SubCategories = FindSubCategories(categs, r.CategoryId)
                                ));

            return firstLevels;
        }

        //for now this only supports 2 levels, for more levels, just call this function recursively
        IEnumerable<Category> FindSubCategories(IEnumerable<Category> categs, short parentId)
        {
            return
                (from r in categs
                 where r.ParentId == parentId
                 orderby r.Name
                 select new Category(r.CategoryId, r.ParentId, r.Name));
        }

        public virtual Category GetCategoryById(int id)
        {
            Category cat = null;

            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                using (SqlCommand cmd = new SqlCommand("sp_category_get_by_id", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("categoryId", id);
                    conn.Open();

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        reader.Read();

                        if (!reader.HasRows)
                            return null;

                        short? parentId = null;

                        if (reader["ParentId"].GetType() != typeof(DBNull))
                            parentId = reader.GetInt16("ParentId");

                        cat = new Category(reader.GetInt16("CategoryId"),
                                           parentId,
                                           reader.GetString("Name"));
                    }
                }
            }

            return cat;
        }

        public virtual Category GetCategoryByName(string name)
        {
            Category cat = null;

            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                using (SqlCommand cmd = new SqlCommand("sp_category_get_by_name", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("category", name);
                    conn.Open();

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        reader.Read();

                        if (!reader.HasRows)
                            return null;
                        short? parentId = null;

                        if (reader["ParentId"].GetType() != typeof(DBNull))
                            parentId = reader.GetInt16("ParentId");

                        cat = new Category(reader.GetInt16("CategoryId"),
                                           parentId,
                                           reader.GetString("Name"));
                    }
                }
            }

            return cat;
        }


        public virtual Tag GetTagById(int id)
        {
            Tag tag = null;

            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                using (SqlCommand cmd = new SqlCommand("sp_tag_get_by_id", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("tagId", id);
                    conn.Open();

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        reader.Read();

                        if (!reader.HasRows)
                            return null;

                        tag = new Tag(reader.GetInt32("TagId"), reader.GetString("Name"));
                    }
                }
            }

            return tag;
        }

        public virtual IEnumerable<Tag> GetTags(int top)
        {
            List<Tag> tags = new List<Tag>();

            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                using (SqlCommand cmd = new SqlCommand("sp_tag_get_tops", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("top", top);
                    conn.Open();

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Tag tag = new Tag
                                          {
                                              StoryCount = reader.GetInt32("Count"),
                                              TagId = reader.GetInt32("TagId"),
                                              Name = reader.GetString("Name")
                                          };

                            tags.Add(tag);
                        }
                    }
                }
            }

            return tags;
        }

        public virtual TagList GetAllTags(string search, TagListOrderBy orderBy, int start, int max)
        {
            var tags = new TagList();

            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                using (SqlCommand cmd = new SqlCommand("sp_tag_get_all", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("search", (String.IsNullOrEmpty(search)) ? "" : search);
                    cmd.Parameters.AddWithValue("orderBy", orderBy);
                    cmd.Parameters.AddWithValue("start", start);
                    cmd.Parameters.AddWithValue("max", max);
                    cmd.Parameters.AddWithValue("totalCount", SqlDbType.Int);
                    cmd.Parameters["totalCount"].Direction = ParameterDirection.Output;
                    conn.Open();

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Tag tag = new Tag
                            {
                                StoryCount = reader.GetInt32("Count"),
                                TagId = reader.GetInt32("TagId"),
                                Name = reader.GetString("Name")
                            };

                            tags.Add(tag);
                        }
                    }

                    tags.TotalCount = Convert.ToInt32(cmd.Parameters["totalCount"].Value);
                }
            }

            return tags;
        }

        public virtual IEnumerable<TopUser> GetTopMovers(int top, string adminRole, float ptsSubmit, 
                                                         float ptsSubmittedVoted, float ptsPublished, 
                                                         float ptsSubmittedCommented, float ptsComment, 
                                                         float ptsVote, float ptsSubmittedClicked)
        {
            return GetTopUsers("sp_top_movers_get", top, adminRole, ptsSubmit, ptsSubmittedVoted, ptsPublished, 
                               ptsSubmittedCommented, ptsComment, ptsVote, ptsSubmittedClicked);
        }

        public virtual IEnumerable<TopUser> GetTopLeaders(int top, string adminRole, float ptsSubmit,
                                                         float ptsSubmittedVoted,  float ptsPublished,
                                                         float ptsSubmittedCommented,float ptsComment, 
                                                         float ptsVote, float ptsSubmittedClicked)
        {
            return GetTopUsers("sp_top_leaders_get", top, adminRole, ptsSubmit, ptsSubmittedVoted, ptsPublished,
                               ptsSubmittedCommented, ptsComment, ptsVote, ptsSubmittedClicked);
        }

        IEnumerable<TopUser> GetTopUsers(string procName, int top, string adminRole, float ptsSubmit, 
                                         float ptsSubmittedVoted, float ptsPublished, float ptsSubmittedCommented,
                                         float ptsComment, float ptsVote, float ptsSubmittedClicked)
        {
            List<TopUser> topUsers = new List<TopUser>();

            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                using (SqlCommand cmd = new SqlCommand(procName, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("top", top);
                    cmd.Parameters.AddWithValue("adminRole", adminRole);
                    cmd.Parameters.AddWithValue("pts_submit", ptsSubmit);
                    cmd.Parameters.AddWithValue("pts_submit_voted", ptsSubmittedVoted);
                    cmd.Parameters.AddWithValue("pts_submit_published", ptsPublished);
                    cmd.Parameters.AddWithValue("pts_submit_commented", ptsSubmittedCommented);
                    cmd.Parameters.AddWithValue("pts_comment", ptsComment);
                    cmd.Parameters.AddWithValue("pts_vote", ptsVote);
                    cmd.Parameters.AddWithValue("pts_click", ptsSubmittedClicked);
                    conn.Open();

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (!reader.HasRows)
                            return topUsers;

                        int i = 1;
                        while (reader.Read())
                        {
                            topUsers.Add(new TopUser
                                             {
                                                 UserId = reader.GetInt32("UserId"),
                                                 Points = reader.GetDecimal("Points"),
                                                 Name = reader.GetString("UserName"),
                                                 Email = reader.GetString("Email"),
                                                 LastActivity = reader.GetDateTime("LastActivityDate"),
                                                 Ranking = i
                                             });

                            i++;
                        }
                    }
                }
            }

            return topUsers;
        }

        public void UpdateUserRole(int userId, string moderatorRole, float ptsToQualify,
                                   float ptsSubmit, float ptsSubmittedVoted,
                                   float ptsPublished, float ptsSubmittedCommented,
                                   float ptsComment, float ptsVote, float ptsSubmittedClicked)
        {
            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                using (SqlCommand cmd = new SqlCommand("sp_update_user_role", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("userId", userId);
                    cmd.Parameters.AddWithValue("moderatorRole", moderatorRole);
                    cmd.Parameters.AddWithValue("ptsToQualify", ptsToQualify);
                    cmd.Parameters.AddWithValue("pts_submit", ptsSubmit);
                    cmd.Parameters.AddWithValue("pts_submit_voted", ptsSubmittedVoted);
                    cmd.Parameters.AddWithValue("pts_submit_published", ptsPublished);
                    cmd.Parameters.AddWithValue("pts_submit_commented", ptsSubmittedCommented);
                    cmd.Parameters.AddWithValue("pts_comment", ptsComment);
                    cmd.Parameters.AddWithValue("pts_vote", ptsVote);
                    cmd.Parameters.AddWithValue("pts_click", ptsSubmittedClicked);
                    conn.Open();

                    cmd.ExecuteNonQuery();
                }
            }
        }

        public virtual int GetUserId(object identifier)
        {
            int id = 0;

            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                using (SqlCommand cmd = new SqlCommand("sp_user_get_id", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("userId", identifier);
                    conn.Open();

                    id = Convert.ToInt32(cmd.ExecuteScalar());
                }
            }

            return id;
        }

        public virtual int GetUserIdByName(string name)
        {
            int id = 0;

            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                using (SqlCommand cmd = new SqlCommand("sp_user_get_id_by_name", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("name", name);
                    conn.Open();

                    id = Convert.ToInt32(cmd.ExecuteScalar());
                }
            }

            return id;
        }

        public virtual object GetProviderUserKey(int userId)
        {
            object id;

            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                using (SqlCommand cmd = new SqlCommand("sp_user_get_provider_key", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("userId", userId);
                    conn.Open();

                    id = cmd.ExecuteScalar();
                }
            }

            return id;
        }

        public virtual int GetVoteCount(int storyId)
        {
            int count = 0;

            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                using (SqlCommand cmd = new SqlCommand("sp_story_get_vote_count", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("storyId", storyId);
                    conn.Open();

                    count = Convert.ToInt32(cmd.ExecuteScalar());
                }
            }

            return count;
        }

        public virtual int GetVoteCountByUrl(string url)
        {
            string urlHash = url.GetURLHash();

            int count = 0;

            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                using (SqlCommand cmd = new SqlCommand("sp_story_get_vote_count_by_url", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("urlHash", urlHash);
                    conn.Open();

                    count = Convert.ToInt32(cmd.ExecuteScalar());
                }
            }

            return count;
        }

        public virtual string GetStoryUrlById(int storyId)
        {
            string url;

            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                using (SqlCommand cmd = new SqlCommand("sp_story_get_url", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("storyId", storyId);
                    conn.Open();

                    object res = cmd.ExecuteScalar();

                    url = res.ToString();
                }
            }

            return url;
        }

        public virtual int GetStoryByUrl(string url)
        {
            string urlHash = url.GetURLHash();
            int storyId = -1;

            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                using (SqlCommand cmd = new SqlCommand("sp_story_get_by_url", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("urlHash", urlHash);
                    conn.Open();

                    object res = cmd.ExecuteScalar();

                    storyId = (res != null) ? Convert.ToInt32(res) : -1;
                }
            }

            return storyId;
        }

        public virtual IEnumerable<TopStory> GetTopStoriesInCategory(int categoryId, int top)
        {
            List<TopStory> topStories = new List<TopStory>();

            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                using (SqlCommand cmd = new SqlCommand("sp_story_get_top_in_category", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("categoryId", categoryId);
                    cmd.Parameters.AddWithValue("top", top);
                    conn.Open();

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            TopStory topStory = new TopStory
                            {
                                StoryId = reader.GetInt32("StoryId"),
                                Title = reader.GetString("Title"),
                                VoteCount = reader.GetInt32("VoteCount")
                            };

                            topStories.Add(topStory);
                        }
                    }
                }
            }

            return topStories;
        }

        public virtual IEnumerable<StoryRelated> GetRelatedStories(int storyId, int top)
        {
            List<StoryRelated> relatedStories = new List<StoryRelated>();

            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                using (SqlCommand cmd = new SqlCommand("sp_story_get_related", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("storyId", storyId);
                    cmd.Parameters.AddWithValue("top", top);
                    conn.Open();

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            StoryRelated storyRelated = new StoryRelated {
                                                                             StoryId = reader.GetInt32("StoryId"),
                                                                             Title = reader.GetString("Title"),
                                                                             VoteCount = reader.GetInt32("VoteCount"),
                                                                             Url = reader.GetString("Url"),
                                                                             PublishedOn = reader.GetDateTime("PublishedOn")
                                                                         };

                            relatedStories.Add(storyRelated);
                        }
                    }
                }
            }

            return relatedStories;
        }

        public virtual string GetStoryTitle(int storyId)
        {
            String title;

            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                using (SqlCommand cmd = new SqlCommand("sp_story_get_title", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("storyId", storyId);
                    conn.Open();

                    title = Convert.ToString(cmd.ExecuteScalar());
                }
            }

            return title;
        }

        public virtual StoryDetail GetStoryDetailById(int userId, int storyId)
        {
            //----------- Get the story
            StoryDetail story = null;

            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                using (SqlCommand cmd = new SqlCommand("sp_story_get_detail", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("storyId", storyId);
                    conn.Open();

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (!reader.HasRows)
                            return null;

                        reader.Read();

                        User user = new User
                                        {
                                            UserId = reader.GetInt32("PostedBy"),
                                            Email = reader.GetString("Email"),
                                            Name = reader.GetString("UserName")
                                        };

                        story = new StoryDetail
                                    {
                                        StoryId = reader.GetInt32("StoryId"),
                                        Title = reader.GetString("Title"),
                                        Description = reader.GetString("Description"),
                                        Url = reader.GetString("Url"),
                                        CategoryName = reader.GetString("Category"),
                                        CategoryId = reader.GetInt16("CategoryId"),
                                        PostedOn = reader.GetDateTime("PostedOn"),
                                        VoteCount = reader.GetInt32("VoteCount"),
                                        PostedByUser = user,
                                        HasVoted = false
                                    };

                        if (reader["PublishedOn"].GetType() != typeof(DBNull))
                            story.PublishedOn = reader.GetDateTime("PublishedOn");
                    }
                }

                //------------- Get the Votes
                List<User> uservotes = new List<User>();

                using (SqlCommand cmd = new SqlCommand("sp_story_get_votes", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("storyId", storyId);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            int uid = reader.GetInt32("VotedBy");

                            if (uid == userId)
                                story.HasVoted = true;

                            User u = new User
                                         {
                                             UserId = uid,
                                             Name = reader.GetString("UserName"),
                                             Email = reader.GetString("Email")
                                         };

                            uservotes.Add(u);
                        }
                    }
                }

                story.VotedBy = uservotes;

                //------------- Get the Comments
                List<Comment> comments = new List<Comment>();

                using (SqlCommand cmd = new SqlCommand("sp_story_get_comments", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("storyId", storyId);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Comment comment = new Comment
                                                  {
                                                      Content = reader.GetString("Text"),
                                                      PostedByUser = new User
                                                                         {
                                                                             UserId = reader.GetInt32("PostedBy"),
                                                                             Email = reader.GetString("Email"),
                                                                             Name = reader.GetString("UserName")
                                                                         },
                                                      PostedOn = reader.GetDateTime("PostedOn")
                                                  };

                            comments.Add(comment);
                        }
                    }
                }

                story.Comments = comments;
                story.Tags = GetStoryTags(storyId);
            }

            return story;
        }

        public virtual StoryList GetPublishedStoriesForAllCategory(int userId, int sortDays, int start, int max)
        {
            StoryList items = null;

            using (SqlCommand cmd = new SqlCommand("sp_story_get_published_for_all_category"))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("days", sortDays);
                cmd.Parameters.AddWithValue("start", start);
                cmd.Parameters.AddWithValue("max", max);
                cmd.Parameters.AddWithValue("count", SqlDbType.Int);
                cmd.Parameters["count"].Direction = ParameterDirection.Output;

                items = PrepareStories(cmd, userId);
                items.TotalCount = Convert.ToInt32(cmd.Parameters["count"].Value);
            }

            return items;
        }

        public virtual StoryList GetPublishedStoriesForCategory(int userId, int categoryId, int sortDays, int start, int max)
        {
            StoryList items = null;

            using (SqlCommand cmd = new SqlCommand("sp_story_get_published_for_category"))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("categoryId", categoryId);
                cmd.Parameters.AddWithValue("days", sortDays);
                cmd.Parameters.AddWithValue("start", start);
                cmd.Parameters.AddWithValue("max", max);
                cmd.Parameters.AddWithValue("count", SqlDbType.Int);
                cmd.Parameters["count"].Direction = ParameterDirection.Output;

                items = PrepareStories(cmd, userId);
                items.TotalCount = Convert.ToInt32(cmd.Parameters["count"].Value);
            }

            return items;
        }

        public virtual StoryList GetStoriesForTag(int userId, int tagId, int sortDays, int start, int max)
        {
            StoryList items = null;

            using (SqlCommand cmd = new SqlCommand("sp_story_get_by_tag"))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("tagId", tagId);
                cmd.Parameters.AddWithValue("days", sortDays);
                cmd.Parameters.AddWithValue("start", start);
                cmd.Parameters.AddWithValue("max", max);
                cmd.Parameters.AddWithValue("count", SqlDbType.Int);
                cmd.Parameters["count"].Direction = ParameterDirection.Output;

                items = PrepareStories(cmd, userId);
                items.TotalCount = Convert.ToInt32(cmd.Parameters["count"].Value);
            }

            return items;
        }

        public virtual StoryList GetUpcomingStories(int userId, int sortDays, int start, int max)
        {
            StoryList items;

            using (SqlCommand cmd = new SqlCommand("sp_story_get_upcoming"))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("days", sortDays);
                cmd.Parameters.AddWithValue("start", start);
                cmd.Parameters.AddWithValue("max", max);
                cmd.Parameters.AddWithValue("count", SqlDbType.Int);
                cmd.Parameters["count"].Direction = ParameterDirection.Output;

                items = PrepareStories(cmd, userId);
                items.TotalCount = Convert.ToInt32(cmd.Parameters["count"].Value);
            }

            return items;
        }

        public virtual StoryList GetStoriesPostedByUser(int userId, int postedByUserId, int sortDays, int start, int max)
        {
            StoryList items;

            using (SqlCommand cmd = new SqlCommand("sp_story_get_by_user"))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("userId", postedByUserId);
                cmd.Parameters.AddWithValue("days", sortDays);
                cmd.Parameters.AddWithValue("start", start);
                cmd.Parameters.AddWithValue("max", max);
                cmd.Parameters.AddWithValue("count", SqlDbType.Int);
                cmd.Parameters["count"].Direction = ParameterDirection.Output;

                items = PrepareStories(cmd, userId);
                items.TotalCount = Convert.ToInt32(cmd.Parameters["count"].Value);
            }

            return items;
        }

        public virtual StoryList SearchStories(int userId, string query, int start, int max)
        {
            StoryList items;

            using (SqlCommand cmd = new SqlCommand("sp_story_search"))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("query", query);
                cmd.Parameters.AddWithValue("start", start);
                cmd.Parameters.AddWithValue("max", max);
                cmd.Parameters.AddWithValue("count", SqlDbType.Int);
                cmd.Parameters["count"].Direction = ParameterDirection.Output;

                items = PrepareStories(cmd, userId);
                items.TotalCount = Convert.ToInt32(cmd.Parameters["count"].Value);
            }

            return items;
        }

        StoryList PrepareStories(SqlCommand cmd, int userId)
        {
            StoryList items = new StoryList();

            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                cmd.Connection = conn;
                conn.Open();

                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Story story = new Story
                                          {
                                              StoryId = reader.GetInt32("StoryId"),
                                              Title = reader.GetString("Title"),
                                              Description = reader.GetString("Description"),
                                              Url = reader.GetString("Url"),
                                              CategoryName = reader.GetString("Category"),
                                              CategoryId = reader.GetInt16("CategoryId"),
                                              PostedByUser = new User
                                                                 {
                                                                     UserId = reader.GetInt32("PostedBy"),
                                                                     Name = reader.GetString("UserName"),
                                                                     Email = reader.GetString("Email")
                                                                 },
                                              PostedOn = reader.GetDateTime("PostedOn"),
                                              VoteCount = reader.GetInt32("VoteCount"),
                                              CommentCount = reader.GetInt32("CommentCount")
                                          };

                        if (reader["PublishedOn"].GetType() != typeof(DBNull))
                            story.PublishedOn = reader.GetDateTime("PublishedOn");

                        items.Add(story);
                    }
                }

                SetUserHasVotedInStories(conn, items, userId);
                SetTagsForStories(conn, items);
            }

            return items;
        }

        protected static void SetTagsForStories(SqlConnection conn, StoryList items)
        {
            if (items.Count == 0)
                return;

            string[] stories = items.Select(f => f.StoryId.ToString()).ToArray();
            string inStories = String.Join(", ", stories);

            string command = @"SELECT st.StoryId, t.TagId, t.Name
                               FROM StoryTag st 
                               INNER JOIN Tag t 
                                   ON t.TagId = st.TagId
                               WHERE st.StoryId IN ({0})
                               ORDER BY st.StoryId, t.Name";
            
            Dictionary<int, List<Tag>> tags = new Dictionary<int, List<Tag>>();

            using (SqlCommand cmd = new SqlCommand(command.FormatWith(inStories), conn))
            {
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            int storyId = reader.GetInt32("StoryId");

                            Tag tag = new Tag
                                          {
                                              Name = reader.GetString("Name"),
                                              TagId = reader.GetInt32("TagId")
                                          };

                            if (!tags.ContainsKey(storyId))
                                tags.Add(storyId, new List<Tag>(1));

                            tags[storyId].Add(tag);
                        }
                    }
                }
            }

            foreach (Story item in items)
                if (tags.ContainsKey(item.StoryId))
                    item.Tags = tags[item.StoryId];
                else
                    item.Tags = new List<Tag>();
        }

        public int SubmitStory(string url, string title, int categoryId, string description,
                               string tags, int userId)
        {
            return SubmitStory(url, title, categoryId, description, tags, userId, DateTime.UtcNow);
        }

        public int SubmitStory(string url, string title, int categoryId, string description, 
                               string tags, int userId, DateTime dateTime)
        {
            url = url.Trim();
            string urlHash = url.GetURLHash();

            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                conn.Open();

                using (SqlTransaction tran = conn.BeginTransaction())
                {
                    try
                    {
                        int storyid = -1;

                        using (SqlCommand cmd = new SqlCommand("sp_story_insert", conn))
                        {
                            cmd.Transaction = tran;
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.Parameters.AddWithValue("url", url);
                            cmd.Parameters.AddWithValue("urlHash", urlHash);
                            cmd.Parameters.AddWithValue("title", title.Trim());
                            cmd.Parameters.AddWithValue("description", description.Trim());
                            cmd.Parameters.AddWithValue("categoryid", categoryId);
                            cmd.Parameters.AddWithValue("postedby", userId);
                            cmd.Parameters.AddWithValue("date", dateTime);

                            storyid = Convert.ToInt32(cmd.ExecuteScalar());
                        }

                        if (storyid == -1)
                        {
                            throw new InvalidOperationException("Story already exists");
                        }

                        //insert new tags
                        if (!string.IsNullOrEmpty(tags))
                        {
                            foreach(string tagName in GetTagsFromString(tags))
                            {
                                using (SqlCommand cmd = new SqlCommand("sp_tag_insert", conn))
                                {
                                    cmd.Transaction = tran;
                                    cmd.CommandType = CommandType.StoredProcedure;
                                    cmd.Parameters.AddWithValue("storyId", storyid);
                                    cmd.Parameters.AddWithValue("tag", tagName);
                                    cmd.ExecuteNonQuery();
                                }
                            }
                        }

                        tran.Commit();

                        return storyid;
                    }
                    catch (Exception)
                    {
                        tran.Rollback();
                        throw;
                    }
                }
            }
        }

        public void UpdateStory(int id, string url, string title, int categoryId, string description, string tags)
        {
            url = url.Trim();
            string urlHash = url.GetURLHash();

            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                conn.Open();

                using (SqlTransaction tran = conn.BeginTransaction())
                {
                    try
                    {
                        using (SqlCommand cmd = new SqlCommand("sp_story_update", conn))
                        {
                            cmd.Transaction = tran;
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.Parameters.AddWithValue("storyId", id);
                            cmd.Parameters.AddWithValue("url", url);
                            cmd.Parameters.AddWithValue("urlHash", urlHash);
                            cmd.Parameters.AddWithValue("title", title.Trim());
                            cmd.Parameters.AddWithValue("description", description.Trim());
                            cmd.Parameters.AddWithValue("categoryId", categoryId);
                            cmd.ExecuteNonQuery();
                        }

                        var storyTags = GetStoryTags(id);
                        string[] tagsArr = GetTagsFromString(tags);

                        //delete removed tags
                        var delTags = (from t in storyTags
                                       where !tagsArr.Contains(t.Name)
                                       select t.TagId);

                        foreach (int tagId in delTags)
                        {
                            using (SqlCommand cmd = new SqlCommand("sp_tag_delete", conn))
                            {
                                cmd.Transaction = tran;
                                cmd.CommandType = CommandType.StoredProcedure;
                                cmd.Parameters.AddWithValue("storyId", id);
                                cmd.Parameters.AddWithValue("tagId", tagId);
                                cmd.ExecuteNonQuery();
                            }
                        }

                        //insert new tags
                        var newTags = (from t in tagsArr
                                       where !(from t2 in storyTags select t2.Name).Contains(t)
                                       select t);

                        foreach (string tag in newTags)
                        {
                            using (SqlCommand cmd = new SqlCommand("sp_tag_insert", conn))
                            {
                                cmd.Transaction = tran;
                                cmd.CommandType = CommandType.StoredProcedure;
                                cmd.Parameters.AddWithValue("storyId", id);
                                cmd.Parameters.AddWithValue("tag", tag);
                                cmd.ExecuteNonQuery();
                            }
                        }

                        tran.Commit();
                    }
                    catch (Exception)
                    {
                        tran.Rollback();
                        throw;
                    }
                }
            }
        }


        public bool VoteInStory(int storyId, int userId, int qualifyingVote)
        {
            bool published = false;

            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                conn.Open();

                using (SqlTransaction tran = conn.BeginTransaction())
                {
                    try
                    {
                        using (SqlCommand cmd = new SqlCommand("sp_story_vote", conn))
                        {
                            cmd.Transaction = tran;
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.Parameters.AddWithValue("storyId", storyId);
                            cmd.Parameters.AddWithValue("userId", userId);
                            cmd.Parameters.AddWithValue("qualify", qualifyingVote);
                            cmd.Parameters.AddWithValue("published", SqlDbType.Bit);
                            cmd.Parameters["published"].Direction = ParameterDirection.Output;

                            cmd.ExecuteScalar();

                            published = Convert.ToBoolean(cmd.Parameters["published"].Value);

                            tran.Commit();
                        }
                    }
                    catch (Exception)
                    {
                        tran.Rollback();
                        throw;
                    }
                }
            }

            return published;
        }

        public void StoryClick(int storyId)
        {
            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                using (SqlCommand cmd = new SqlCommand("sp_story_click", conn))
                {
                    conn.Open();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("storyId", storyId);
                    cmd.ExecuteNonQuery();
                }
            }
        }

        public void StoryPublish(int storyId)
        {
            InternalStoryPublish(storyId, DateTime.UtcNow);
        }

        public void StoryPublish(int storyId, DateTime date)
        {
            InternalStoryPublish(storyId, date);
        }

        void InternalStoryPublish(int storyId, DateTime date)
        {
            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                conn.Open();
                using (SqlTransaction tran = conn.BeginTransaction())
                {
                    try
                    {
                        using (SqlCommand cmd = new SqlCommand("sp_story_publish", conn))
                        {
                            cmd.Transaction = tran;
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.Parameters.AddWithValue("storyId", storyId);
                            cmd.Parameters.AddWithValue("date", date);
                            cmd.ExecuteNonQuery();
                        }

                        tran.Commit();
                    }
                    catch (Exception)
                    {
                        tran.Rollback();
                        throw;
                    }
                }
            }
        }

        public void StoryDelete(int storyId)
        {
            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                conn.Open();
                using (SqlTransaction tran = conn.BeginTransaction())
                {
                    try
                    {
                        using (SqlCommand cmd = new SqlCommand("sp_story_delete", conn))
                        {
                            cmd.Transaction = tran;
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.Parameters.AddWithValue("storyId", storyId);
                            cmd.ExecuteNonQuery();
                        }

                        tran.Commit();
                    }
                    catch (Exception)
                    {
                        tran.Rollback();
                        throw;
                    }
                }
            }
        }

        public int PostComment(int storyId, int userId, string content)
        {
            int id = 0;

            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                conn.Open();
                using (SqlTransaction tran = conn.BeginTransaction())
                {
                    try
                    {
                        using (SqlCommand cmd = new SqlCommand("sp_comment_insert", conn))
                        {
                            cmd.Transaction = tran;
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.Parameters.AddWithValue("storyId", storyId);
                            cmd.Parameters.AddWithValue("postedBy", userId);
                            cmd.Parameters.AddWithValue("comment", content);

                            id = Convert.ToInt32(cmd.ExecuteScalar());
                        }

                        tran.Commit();
                    }
                    catch (Exception)
                    {
                        tran.Rollback();
                        throw;
                    }
                }
            }

            return id;
        }

        public int AddStoryPending(string url, string title, int categoryId, string summary,
                                    string tags, DateTime publishDate)
        {
            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand("sp_story_pending_add", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("url", url);
                    cmd.Parameters.AddWithValue("title", title);
                    cmd.Parameters.AddWithValue("categoryId", categoryId);
                    cmd.Parameters.AddWithValue("summary", summary);
                    cmd.Parameters.AddWithValue("tags", tags);
                    cmd.Parameters.AddWithValue("publishDate", publishDate);

                    return cmd.ExecuteNonQuery();
                }
            }
        }

        static string[] GetTagsFromString(string tags)
        {
            //LINQ black magic
            return tags.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries)
                       .Select(t => t.Trim())
                       .Distinct(StringComparer.InvariantCultureIgnoreCase)
                       .Where(f => f.Length >= 1)
                       .ToArray();
        }

        IEnumerable<Tag> GetStoryTags(int storyId)
        {
            List<Tag> tags = new List<Tag>();

            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                //---------- Get the Tags
                using (SqlCommand cmd = new SqlCommand("sp_story_get_tags", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("storyId", storyId);
                    conn.Open();

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            tags.Add(new Tag(reader.GetInt32("TagId"), reader.GetString("Name")));
                        }
                    }
                }
            }

            return tags;
        }

        protected static void SetUserHasVotedInStories(SqlConnection conn, StoryList items, int userId)
        {
            if (items.Count == 0)
                return;

            //If the user is anonymous, just set all to false
            if (userId == 0)
            {
                foreach (Story item in items)
                    item.HasVoted = false;

                return;
            }

            string[] stories = items.Select(f => f.StoryId.ToString()).ToArray();
            string inStories = String.Join(", ", stories);

            string command = @"SELECT StoryId
                               FROM Vote
                               WHERE StoryId IN ({0})
                               AND VotedBy = @userId";

            List<int> votedStories = new List<int>();

            using (SqlCommand cmd = new SqlCommand(command.FormatWith(inStories), conn))
            {
                cmd.Parameters.AddWithValue("userId", userId);

                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            int storyId = reader.GetInt32("StoryId");
                            votedStories.Add(storyId);
                        }
                    }
                }
            }

            foreach (Story item in items)
                item.HasVoted = votedStories.Contains(item.StoryId);
        }

        public int ExecuteNonQuery(IDbCommand cmd)
        {
            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                cmd.Connection = conn;
                conn.Open();

                return cmd.ExecuteNonQuery();
            }
        }
    }
}